/**
 * @name         RepNET Reporting Engine Main Controller
 * @version      1.0a
 * @author       Kasun Rathnayaka <kazar55@gmail.com>
 * @author       Ramindu Deshapriya <rasade88@gmail.com>
 * @author 		 Prabodha Dissanayake <prabodha89@gmail.com>
 * @author 		 Thusira Dissanayake <thusira14@gmail.com>
 * @about        Developed by MiDi
 * @license	 http://www.gnu.org/licenses/lgpl-2.1.html GNU Lesser General Public License (LGPL)
 * @lastModified 2012.09.24
 */
package com.midi.wholesale.main;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import com.midi.wholesale.main.Main;
import com.midi.wholesale.models.*;

/**
 * Class to handle reports and views
 * 
 * @author Prabodha
 * 
 */
public class Reports {

	static String sql;
	static ResultSet rs;
	static Statement statement;
	static Statement regionStatement;

/*	public static String getNextDate(String d) {

		int oneDay = 1000 * 60 * 60 * 24;
		String nextDate = "";
		// Date date = new Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		Date currDate;
		try {
			currDate = dateFormat.parse(d);
			nextDate = dateFormat.format(currDate.getTime() + oneDay);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return nextDate;
	}*/

	public static ArrayList<DeliveryResults> getDeliverySummary(
			String fromDate, String toDate) {
		System.out.println(fromDate+"W"+toDate);
		ArrayList<DeliveryResults> resultsList = new ArrayList<DeliveryResults>();

		try {
			statement = Main.dbConnection.createStatement();
			sql = "SELECT * FROM `deliveryinfo` where date BETWEEN '"+fromDate+"'AND'"+toDate+"'";					
			rs = statement.executeQuery(sql);

			while (rs.next()) {

				ResultSet routeName = Main.dbConnection.createStatement()
						.executeQuery(
								"select name from routs where id='"
										+ rs.getInt("route_id") + "'");

				ResultSet agentName = Main.dbConnection.createStatement()
						.executeQuery(
								"select name from agents where id='"
										+ rs.getInt("agent_id") + "'");

				ResultSet lorrytName = Main.dbConnection.createStatement()
						.executeQuery(
								"select name from lorries where id='"
										+ rs.getInt("lorry_id") + "'");

				ResultSet itemtName = Main.dbConnection.createStatement()
						.executeQuery(
								"select name from items where id='"
										+ rs.getInt("item_id") + "'");

				if (routeName.next() && agentName.next() && lorrytName.next()
						&& itemtName.next()) {

					DeliveryResults cr = new DeliveryResults(
							routeName.getString("name"),
							agentName.getString("name"),
							lorrytName.getString("name"),
							rs.getString("description"),
							itemtName.getString("name"), rs.getInt("amount"),
							rs.getInt("remainder"), rs.getString("date"));

					resultsList.add(cr);
				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return resultsList;

	}
	
	public static ArrayList<DeliveryResults> getDeliverySummaryByItems(
			String fromDate, String toDate,Agent agent) {
		System.out.println(fromDate+"W"+toDate);
		ArrayList<DeliveryResults> resultsList = new ArrayList<DeliveryResults>();

		try {
			statement = Main.dbConnection.createStatement();
			sql = "SELECT i.name,SUM(d.amount-d.remainder) AS amount FROM deliveryinfo AS  d INNER JOIN items i ON d.item_id=i.id INNER JOIN agents ON d.agent_id=agents.id WHERE d.date BETWEEN '"+fromDate+"'AND'"+toDate+"' AND agents.id='"+agent.getId()+"' GROUP BY d.item_id";		
			//get total by agent
			//SELECT i.name,SUM(d.amount-d.remainder) AS amount FROM deliveryinfo AS  d INNER JOIN items i ON d.item_id=i.id INNER JOIN agents ON d.agent_id=agents.id WHERE d.date BETWEEN '"+fromDate+"'AND'"+toDate+"' AND agents.id='"+agent.agent.getId()+"' GROUP BY d.item_id
			rs = statement.executeQuery(sql);

			while (rs.next()) {		

					DeliveryResults cr = new DeliveryResults(
							rs.getString("name"),rs.getInt("amount"));//amount=amount-remainder

					resultsList.add(cr);
				

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return resultsList;

	}

	

}
